﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using DirectorioExalmarOledbHelper;
using DirectorioExalmarEntidades;
using DirectorioExalmarInterfaces;
using System.Configuration;

namespace DirectorioExalmarAccesoDatos
{
    public class Empleado : IEmpleado
    {
        #region IEmpleado Members

        private readonly String _sCadenaConexion = ConfigurationManager.ConnectionStrings["conDirectorioExalmar"].ConnectionString;

        public EmpleadoInfo Consultar(EmpleadoInfo oEmpleado)
        {
            throw new NotImplementedException();
        }

        public IList<EmpleadoInfo> Listar(EmpleadoInfo oEmpleado)
        {
            var query =
                @"Select Nombre, Apellido_Paterno, Apellido_Materno, Id_Cargo, Cargo, Id_Area, Area, Id_Sede, Sede, Nro_Telefono_Fijo, " +
                "Nro_Anexo, Nro_Celular_1, Nro_Celular_2, IdEmpleado From ConsultaEmpleado";

            if (oEmpleado != null)
            {
                var where = " where ";
                if (!String.IsNullOrEmpty(oEmpleado.Nombres))
                    where += " Nombre Like '%" + oEmpleado.Nombres + "%' ";
                if (!String.IsNullOrEmpty(oEmpleado.ApellidoPaterno))
                {
                    if (!where.Equals(" where "))
                        where += " and ";
                    where += " Apellido_Paterno Like '%" + oEmpleado.ApellidoPaterno + "%'";
                }
                if (!String.IsNullOrEmpty(oEmpleado.ApellidoMaterno))
                {
                    if (!where.Equals(" where "))
                        where += " and ";
                    where += " Apellido_Materno Like '%" + oEmpleado.ApellidoMaterno + "%'";
                }
                if (oEmpleado.IdCargo.HasValue)
                {
                    if (!where.Equals(" where "))
                        where += " and ";
                    where += " Id_Cargo = " + oEmpleado.IdCargo.ToString();
                }
                if (oEmpleado.IdArea.HasValue)
                {
                    if (!where.Equals(" where "))
                        where += " and ";
                    where += " Id_Area = " + oEmpleado.IdArea.ToString();
                }
                if (oEmpleado.IdSede.HasValue)
                {
                    if (!where.Equals(" where "))
                        where += " and ";
                    where += " Id_Sede = " + oEmpleado.IdSede.ToString();
                }

                query += where;
            }
            var oListaEmplaedos = new List<EmpleadoInfo>();

            using (var drd = OleDbHelper.ExecuteReader(_sCadenaConexion, CommandType.Text, query))
            {
                if (drd != null)
                {
                    if (drd.HasRows)
                    {
                        while (drd.Read())
                        {
                            oListaEmplaedos.Add(new EmpleadoInfo(drd.GetInt32(13), drd.IsDBNull(0) ? "" : drd.GetString(0).Trim(), drd.IsDBNull(1) ? "" : drd.GetString(1).Trim(), drd.IsDBNull(2) ? "" : drd.GetString(2).Trim(),
                                Int32.Parse(drd.GetValue(3).ToString()), new CargoInfo(Int32.Parse(drd.GetValue(3).ToString()), drd.GetString(4).Trim()),
                                Int32.Parse(drd.GetValue(5).ToString()), new AreaInfo(Int32.Parse(drd.GetValue(5).ToString()), drd.GetString(6).Trim()),
                                Int32.Parse(drd.GetValue(7).ToString()), new SedeInfo(Int32.Parse(drd.GetValue(7).ToString()), drd.GetString(8)),
                                drd.GetString(9).Trim(), drd.GetString(10).Trim(), drd.GetString(11).Trim(), drd.GetString(12).Trim()));
                        }
                    }
                }
            }
            return oListaEmplaedos;
        }

        public bool Registrar(EmpleadoInfo oEmpleado)
        {
            var bExito = false;

            const string query = "Insert into Empleado (Nombre, Apellido_Paterno, Apellido_Materno, Id_Cargo, Id_Area, Id_Sede, Nro_Telefono_Fijo, " +
                                 "Nro_Anexo, Nro_Celular_1, Nro_Celular_2) Values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            var oledbParm = new OleDbParameter[10];

            using (var oledbcon = new OleDbConnection(_sCadenaConexion))
            {
                oledbcon.Open();
                var oledbtran = oledbcon.BeginTransaction(IsolationLevel.ReadCommitted);

                try
                {
                    oledbParm[0] = new OleDbParameter("@Nombre", OleDbType.VarChar) {Value = oEmpleado.Nombres};
                    oledbParm[1] = new OleDbParameter("@Apellido_Paterno", OleDbType.VarChar)
                        {
                            Value = oEmpleado.ApellidoPaterno
                        };
                    oledbParm[2] = new OleDbParameter("@Apellido_Materno", OleDbType.VarChar)
                        {
                            Value = oEmpleado.ApellidoMaterno
                        };
                    oledbParm[3] = new OleDbParameter("@Id_Cargo", OleDbType.Integer) {Value = oEmpleado.IdCargo};
                    oledbParm[4] = new OleDbParameter("@Id_Area", OleDbType.Integer) { Value = oEmpleado.IdArea };
                    oledbParm[5] = new OleDbParameter("@Id_Sede", OleDbType.Integer) { Value = oEmpleado.IdSede };
                    oledbParm[6] = new OleDbParameter("@Nro_Telefono_Fijo", OleDbType.VarChar)
                        {
                            Value = oEmpleado.NroTelefonoFijo
                        };
                    oledbParm[7] = new OleDbParameter("@Nro_Anexo", OleDbType.VarChar) {Value = oEmpleado.NroAnexo};
                    oledbParm[8] = new OleDbParameter("@Nro_Celular1", OleDbType.VarChar) {Value = oEmpleado.NroCelular1};
                    oledbParm[9] = new OleDbParameter("@Nro_Celular2", OleDbType.VarChar) {Value = oEmpleado.NroCelular2};

                    OleDbHelper.ExecuteNonQuery(oledbtran, CommandType.Text, query, oledbParm);
                    bExito = true;

                    if (bExito)
                    {
                        oledbtran.Commit();
                    }
                    else
                    {
                        oledbtran.Rollback();
                    }
                }
                catch
                {
                    oledbtran.Rollback();
                    bExito = false;
                }
                finally
                {
                    oledbcon.Close();
                }
            }


            return bExito;
        }

        public bool Actualizar(EmpleadoInfo oEmpleado)
        {
            var bExito = false;

            const string query = "Update Empleado " +
                                 " Set Nombre = ?, Apellido_Paterno = ?, Apellido_Materno = ?, Id_Cargo = ?, Id_Area = ?, Id_Sede = ?, Nro_Telefono_Fijo = ?, " +
                                 "Nro_Anexo = ?, Nro_Celular_1 = ?, Nro_Celular_2 = ? Where Id = ?";

            var oledbParm = new OleDbParameter[11];

            using (var oledbcon = new OleDbConnection(_sCadenaConexion))
            {
                oledbcon.Open();
                var oledbtran = oledbcon.BeginTransaction(IsolationLevel.ReadCommitted);

                try
                {
                    oledbParm[0] = new OleDbParameter("@Nombre", OleDbType.VarChar) { Value = oEmpleado.Nombres };
                    oledbParm[1] = new OleDbParameter("@Apellido_Paterno", OleDbType.VarChar)
                    {
                        Value = oEmpleado.ApellidoPaterno
                    };
                    oledbParm[2] = new OleDbParameter("@Apellido_Materno", OleDbType.VarChar)
                    {
                        Value = oEmpleado.ApellidoMaterno
                    };
                    oledbParm[3] = new OleDbParameter("@Id_Cargo", OleDbType.Integer) { Value = oEmpleado.IdCargo };
                    oledbParm[4] = new OleDbParameter("@Id_Area", OleDbType.Integer) { Value = oEmpleado.IdArea };
                    oledbParm[5] = new OleDbParameter("@Id_Sede", OleDbType.Integer) { Value = oEmpleado.IdSede };
                    oledbParm[6] = new OleDbParameter("@Nro_Telefono_Fijo", OleDbType.VarChar)
                    {
                        Value = oEmpleado.NroTelefonoFijo
                    };
                    oledbParm[7] = new OleDbParameter("@Nro_Anexo", OleDbType.VarChar) { Value = oEmpleado.NroAnexo };
                    oledbParm[8] = new OleDbParameter("@Nro_Celular1", OleDbType.VarChar) { Value = oEmpleado.NroCelular1 };
                    oledbParm[9] = new OleDbParameter("@Nro_Celular2", OleDbType.VarChar) { Value = oEmpleado.NroCelular2 };

                    oledbParm[10] = new OleDbParameter("@Id", OleDbType.Integer) { Value = oEmpleado.IdEmpleado };

                    OleDbHelper.ExecuteNonQuery(oledbtran, CommandType.Text, query, oledbParm);
                    bExito = true;

                    if (bExito)
                    {
                        oledbtran.Commit();
                    }
                    else
                    {
                        oledbtran.Rollback();
                    }
                }
                catch
                {
                    oledbtran.Rollback();
                    bExito = false;
                }
                finally
                {
                    oledbcon.Close();
                }
            }


            return bExito;
        }

        #endregion
    }
}
